package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.GraduateProjectSource;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.List;
import java.util.TreeSet;

public class GraduateProjectSourceDao {
    //本类的一个对象引用，保存自身对象
    private static GraduateProjectSourceDao graduateProjectSourceDao = null;



    //私有的构造方法，防止其它类创建它的对象
    private GraduateProjectSourceDao() {
    }

    //静态方法，返回本类的惟一对象
    public synchronized static GraduateProjectSourceDao getInstance() {
        return graduateProjectSourceDao == null ? new GraduateProjectSourceDao()
                : graduateProjectSourceDao;
    }


    public static Collection<GraduateProjectSource> findAll(List conditionList, Pagination pagination, Connection connection) throws SQLException {
        //创建集合类对象，用来保存所有记录代表的degree对象
        Collection<GraduateProjectSource> graduateProjectSource = new TreeSet<GraduateProjectSource>();
        //准备预编译的语句
        int totalNum = CommonDao.getInstance().count(connection,"GraduateProjectSource");
        //创建查询的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM GraduateProjectSource ");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if(conditionList!=null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        if(pagination!=null){
            String LimitClause = pagination.toLimitClause(totalNum);
            sql_select.append(LimitClause);
        }
        //执行预编译语句，结果集放在resultSet中
        PreparedStatement pstmt = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet = pstmt.executeQuery();
        //遍历resultSet
        while(resultSet.next()){
            graduateProjectSource.add(new GraduateProjectSource(resultSet.getInt("id"),
                    resultSet.getString("description"),
                    resultSet.getString("no"),
                    resultSet.getString("remarks")));
        }
        JdbcHelper.close(resultSet,pstmt);
        return graduateProjectSource;
    }

    public static GraduateProjectSource find(int id,Connection connection) throws SQLException {

        //创建sql语句
        String find_sql = "select * from GraduateProjectSource where id = ?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(find_sql);
        //为预编译参数赋值
        pstmt.setInt(1,id);
        //根据id查找
        ResultSet resultSet = pstmt.executeQuery();
        resultSet.next();
        GraduateProjectSource graduateProjectSource = new GraduateProjectSource(id,
                resultSet.getString("description"),
                resultSet.getString("no"),
                resultSet.getString("remarks"));
        JdbcHelper.close(resultSet,pstmt);
        return graduateProjectSource;
    }

    public static boolean update(GraduateProjectSource graduateProjectSource,Connection connection) throws SQLException {


        //创建sql语句
        String updateGPC_sql = "update GraduateProjectSource set description=?,no=?,remarks=? where id=?";
        //在该链接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(updateGPC_sql);
        //为预编译参数赋值
        pstmt.setInt(4,graduateProjectSource.getId());
        pstmt.setString(1,graduateProjectSource.getDescription());
        pstmt.setString(2,graduateProjectSource.getNo());
        pstmt.setString(3,graduateProjectSource.getRemarks());
        //根据执行结果，具体判断所要返回的值
        if (pstmt.executeUpdate()==0){
            JdbcHelper.close(null,pstmt);
            return false;
        }else
            JdbcHelper.close(null,pstmt);
        return  true;
    }

    public static boolean add(GraduateProjectSource graduateProjectSource,Connection connection) throws SQLException {

        //创建sql语句
        String addGPC_sql =
                "insert into GraduateProjectSource (description,no,remarks) values " +
                        "(?,?,?);";
        //在该链接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(addGPC_sql);
        //为预编译参数赋值

        pstmt.setString(1,graduateProjectSource.getDescription());
        pstmt.setString(2,graduateProjectSource.getNo());
        pstmt.setString(3,graduateProjectSource.getRemarks());
        //获取执行结果
        int affectedRowNum = pstmt.executeUpdate();
        //根据结果，具体判断所要返回的值
        if (affectedRowNum == 1) {
            JdbcHelper.close(null,pstmt);
            return true;

        } else
            JdbcHelper.close(null,pstmt);
        return false;
    }

    public static boolean delete(int id,Connection connection) throws SQLException{

        //创建sql语句
        String deleteGPC_sql = "DELETE FROM GraduateProjectSource WHERE id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteGPC_sql);
        //为预编译参数赋值
        pstmt.setInt(1,id);
        if (pstmt.executeLargeUpdate() == 1){
            JdbcHelper.close(null,pstmt);
            return true;
        }else {
            JdbcHelper.close(null,pstmt);
            return false;
        }
    }
    public static boolean delete(GraduateProjectSource graduateProjectSource,Connection connection) throws SQLException {

        String deleteGPC_sql = "delete from GraduateProjectSource where no = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteGPC_sql);
        //为预编译参数赋值
        pstmt.setString(1,graduateProjectSource.getNo());
        //并判断是否删除成功
        if(pstmt.executeUpdate() == 0){
            JdbcHelper.close(null,pstmt);
            return false;
        }else {
            JdbcHelper.close(null,pstmt);
            return true;
        }
    }
}